﻿using System;
using System.Data.SQLite;
using Log;

namespace DVDTek_Data_Object.Accessor
{
    internal class Accessor
    {
        #region Members
        private EventLog log = new EventLog("DVDTek", "DataObject");
        private SQLiteConnection sqliteConnection;
        private static Accessor accessor;
        internal static Accessor Instance
        {
            get
            {
                if (accessor == null)
                    accessor = new Accessor();

                return accessor;
            }
        }
        #endregion

        #region Constructors
        private Accessor()
        {
            initDB();
        }
        #endregion

        #region InitMethods
        private void initDB()
        {
            try
            {
                try
                {
                    //On ouvre la connection a la base Sqlite
                    this.sqliteConnection = new SQLiteConnection("Data Source=DVDTek.db");
                    this.sqliteConnection.Open();
                }
                catch (Exception ex)
                {
                    this.log.Error("", ex);
                    throw ex;
                }

                //On crée les tables si elles n'existent pas déjà
                initTables();

                //Suppression des enregistrements inutiles
                cleanDB();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private void initTables()
        {
            try
            {
                if (!tableExists(ActorAccessor.TABLE_NAME))
                    ExecuteNonQuery(ActorAccessor.Instance.CreateTableQuery);

                if (!tableExists(BorrowerAccessor.TABLE_NAME))
                    ExecuteNonQuery(BorrowerAccessor.Instance.CreateTableQuery);

                if (!tableExists(DirectorAccessor.TABLE_NAME))
                    ExecuteNonQuery(DirectorAccessor.Instance.CreateTableQuery);

                if (!tableExists(TypeAccessor.TABLE_NAME))
                    ExecuteNonQuery(TypeAccessor.Instance.CreateTableQuery);

                if (!tableExists(VideoAccessor.TABLE_NAME))
                    ExecuteNonQuery(VideoAccessor.Instance.CreateTableQuery);
            }
            catch (Exception ex)
            {
                this.log.Error("", ex);
                throw ex;
            }
        }

        private void cleanDB()
        {
            try
            {
            }
            catch (Exception ex)
            {
                this.log.Error("", ex);
                throw ex;
            }
        }

        private bool tableExists(string tableName)
        {
            try
            {
                string query = string.Concat(
                    "SELECT name FROM SQLITE_MASTER ",
                    "WHERE type='table'",
                    " AND name='", tableName, "';");
                SQLiteDataReader reader = ExecuteQuery(query);
                bool retVal = reader.HasRows;
                reader.Close();
                return retVal;
            }
            catch (Exception ex)
            {
                this.log.Error("", ex);
                throw ex;
            }
        }
        #endregion

        #region QueryExecutionMethods
        /// <summary>
        /// Execute une requete ne renvoyant pas de resultat.
        /// Ex : CREATE, INSERT,....
        /// </summary>
        /// <param name="query"></param>
        internal void ExecuteNonQuery(string query)
        {
            SQLiteCommand command = this.sqliteConnection.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// Execute une requete qui renvoie un object unique.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        internal object ExecuteScalarQuery(string query)
        {
            SQLiteCommand command = this.sqliteConnection.CreateCommand();
            command.CommandText = query;
            return command.ExecuteScalar();
        }

        /// <summary>
        /// Execute une requete et renvoie un reader permettant de lire les résultats.
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        internal SQLiteDataReader ExecuteQuery(string query)
        {
            SQLiteCommand command = this.sqliteConnection.CreateCommand();
            command.CommandText = query;
            return command.ExecuteReader();
        }
        #endregion
    }
}
